﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;

namespace HsTec.Mobile.Green.Util
{
    public class FormulaAnalyse
    {
        public FormulaAnalyse()
        {

        }

        /// <summary>
        /// 公式分析 
        /// </summary>
        /// <param name="sFormula">预处理公式</param>
        /// <param name="sWhere">条件信息</param>
        /// <returns></returns>
        public string Analyse(string sFormula, string sWhere)
        {
            string tmpFormula = sFormula;
            string tmpSQL = String.Empty;
            string tmpData = String.Empty;
            string[] aryList = { null, null };
            //1.查找公式中的!号 因为主要是通过!号来分隔表和字段的 例如 XXX表!xx字段
            //1.1处理sum函数
            string regexSumStr = @"sum\(\w*!\w*\)";
            string regexAvgStr = @"\w*!\w*";
            string regexFiled = @"\w*!\w*";
            Regex rSum = new Regex(regexSumStr, RegexOptions.None);
            Regex rAvg = new Regex(regexAvgStr, RegexOptions.None);
            Regex rField = new Regex(regexFiled, RegexOptions.None);
            Match mFiled = null;
            //获取所有的匹配项
            MatchCollection mcs = rSum.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                mFiled = rField.Match(mc.ToString());
                aryList = mFiled.ToString().Split('!');
                //TODO 关于数据如果为空如何处理
                tmpSQL = "select sum([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) " + sWhere;
                tmpData = SQLHelper.ExecuteString(tmpSQL);
                tmpFormula = tmpFormula.Replace(mc.ToString(), String.IsNullOrEmpty(tmpData) ? "0" : tmpData);
            }
            //1.2处理avg函数
            mcs = rAvg.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                mFiled = rField.Match(mc.ToString());
                aryList = mFiled.ToString().Split('!');
                tmpSQL = "select avg([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) " + sWhere;
                tmpData = SQLHelper.ExecuteString(tmpSQL);
                tmpFormula = tmpFormula.Replace(mc.ToString(), String.IsNullOrEmpty(tmpData) ? "0" : tmpData);
            }
            //1.3处理普通字段
            mcs = rField.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                aryList = mc.ToString().Split('!');
                tmpSQL = "select sum([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) " + sWhere;
                tmpData = SQLHelper.ExecuteString(tmpSQL);
                tmpFormula = tmpFormula.Replace(mc.ToString(), String.IsNullOrEmpty(tmpData) ? "0" : tmpData);
            }
            //2.输出结果
            String sRtn = String.Empty;
            try
            {
                sRtn = double.Parse(SQLHelper.ExecuteString("select " + tmpFormula)).ToString("0.00");
            }
            catch
            {
                sRtn = "0.00";
            }
            return sRtn;
        }
        /// <summary>
        /// 格式化录入的公式字符串
        /// </summary>
        /// <param name="sFormula">录入字符串</param>
        /// <returns></returns>
        public string FormatFormula(string sFormula)
        {
            string sTmpFormula = sFormula;
            string[] aryList = { null, null };
            string sTable = String.Empty;
            string sField = String.Empty;
            string regexFiled = @"\w*!\w*";
            Regex rField = new Regex(regexFiled, RegexOptions.None);
            MatchCollection mcs = rField.Matches(sTmpFormula);
            foreach (Match mc in mcs)
            {
                aryList = mc.ToString().Split('!');
                if (aryList[0] == "月度报表")
                {
                    string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                                  "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                                  "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                                  "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                                  "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                                  "       AS [default], ISNULL(c.[value], '') AS descript " +
                                  " FROM (SELECT g.id, g.colid " +
                                  "         FROM sysindexes f, sysindexkeys g " +
                                  "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                                  "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                                  "       syscolumns b INNER JOIN " +
                                  "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                                  "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                                  "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                                  " WHERE (a.id = OBJECT_ID('tb_ReportAnalysis')) AND b.name like 'Powers%'" +
                                  " ORDER BY b.colid";
                    DataView mydv = SQLHelper.ExecuteDataView(mysql);
                    for (int i = 0; i < mydv.Count; i++)
                    {
                        if (mydv[i]["descript"].ToString().Replace("-", "") == aryList[1])
                        {
                            sFormula = sFormula.Replace(mc.ToString(), "tb_ReportAnalysis!" + mydv[i]["colname"].ToString());
                            break;
                        }
                    }
                }
                else
                {
                    if (aryList[0] != "中间项")
                    {
                        sTable = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where FieldAlias='" + aryList[0] + "' and FieldChildCount>0");
                        sField = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where FieldAlias='" + aryList[1] + "' and FieldChildCount=0");
                        sFormula = sFormula.Replace(mc.ToString(), "rt_" + sTable + "!" + sField);
                    }
                }
            }
            return sFormula;
        }
    }
}
